Querying Microsoft SQL Server 2014 (M20461) | MSP Training

ABOUT THE PROGRAM

The Querying Microsoft SQL Server 2014 course provides the technical skills to the delegates and enables them to write basic Transact SQL queries. The course includes concepts of SQL Server components and essential capabilities of SQL Server data platform. The training program lays a strong foundation for all SQL Server related disciplines including administration and development of database and business intelligence. The course provides knowledge about sophisticated tools and techniques to the delegates to enhance their performance in online transaction processing and greater insight into the data for better analysis. During the course, the delegates will learn the techniques to perform SELECT statements, data sorting and filtering, grouping and combine data, error handling and data manipulation language.

Apply T-SQL queries to manage database effectively
Courses led by highly certified and well-experienced instructors
Perform data operations like sort, filter, and group and aggregate quickly
MSP Training offers the courses at an affordable price and better value for money
Create and execute dynamic SQL statements
Error handling using TRY/CATCH blocks
Courses delivered using best practices and real world examples

WHAT'S INCLUDED ?

Find out what's included in the training programme.

Key Learning Points

Clear and concise objectives to guide delegates through the course.

Exam Prepration Tips

It helps in preparation for the exam so that they can score well.

Tutor Support

A dedicated tutor will be at your disposal throughout the training to guide you through any issues.

Certificate

Delegates will get certification of completion at the end of the course.

PREREQUISITES

The delegates must have knowledge of core functions of Windows operating system and must be familiar with relational database management system.

TARGET AUDIENCE

Database Developers
Report Writers
Database Administrators
Client Application Developers
Business Intelligence Professionals
Business Analysts

WHAT WILL YOU LEARN?

Understand the basic concepts and structure of Microsoft SQL Server 2014
Classify the components of T-SQL and usage of sets and predicate logic in SELECT statements
Able to remove duplicates and write SELECT statements and CASE expressions
Learn how to consolidate and retrieve data from multiple sources
Techniques to resolve missing and unknown values
Recognize conversions between data types and identify importance of type precedence
Apply manipulation language to insert and modify data
Learn how to invoke system stored procedures

Enquire Program

Fill in the form below & we'd get back to you.

PROGRAM OVERVIEW

Nowadays the source of incoming data is not only restricted to few employees detail files or student details, it has been expanded much more than that. Previously, Organizations tend to store and maintain data in FoxPro, dBase for a longer period of time. Later on, Microsoft Access came into existence. However, these have insufficient capabilities to store the massive bulk of data.

Therefore, the need for applications handling a large amount of data increased with the influx of massive incoming data. Two major applications introduced in the market were SQL Server and Oracle. Oracle became successful overnight but SQL Server took some time to establish itself. Once it was all settled and organized, there was no point of looking behind and it became one of the successful software application across the world. Microsoft SQL Server became an efficient tool for managing database systems in the organization. Transact –SQL (T-SQL) language is extensively used by SQL Server for managing and manipulating data in relational database management system (RDBMS).

Being a Microsoft product SQL Server, it helps in delivering excellent performance and enable the delegates to create and modify data using T-SQL queries. The main aim of the course is to prepare the delegates for the below-mentioned exam and get them certified. The exam serves as a fundamental exam for database administration, business intelligence, and database development.

Key Information of Querying Microsoft SQL Server 2014 (M20461) Exam

In order to complete the course, it is mandatory for delegates to take the exam. The exam comprises below topics:

Creation of database objects: 20-25%

Working with data: 25-30%

Modifying data: 20-25%

Troubleshoot and optimise data: 25-30%

PROGRAM CONTENT

An Introduction to Microsoft SQL Server 2014

Explain SQL Server basic Architecture
SQL Server versions and editions
Work with SQL Server Management Studio

An Introduction to T-SQL Querying

Understand the elements of T-SQL
Describe their role in writing queries
Understand the use of sets
Determine the use of predicate logic in SQL Server
Study the logical order of operations in SELECT statements

Writing SELECT Queries

Understand the techniques to write simple SELECT statements
Exclude duplicates with DISTINCT
Apply table aliases and columns
How to write simple CASE expressions

Querying multiple tables

Apply joins to query multiple tables in a SELECT statement
Write queries with Inner and outer joins
Queries applying self-joins and cross joins

Sorting and filtering data

How to filter data in the WHERE clause using predicates
Sort data using ORDER BY
Use OFFSET FETCH and TOP options for filter data
Work with missing and unknown values

Functioning with SQL Server 2014 Data Types

Classify type precedence, data types, and type conversions
Work with character data types for writing queries
How to use time and date data types for writing queries

Applying DML to Change Data

Add data to your tables
Modify and delete existing records in tables

Handling Built-in Functions

Write queries with use of built in scalar functions
Describe conversion functions
Understand logical functions
Determine functions to work with NULL

Grouping and Aggregating data

Use built in aggregate functions to summarize data
Arrange rows into groups by using GROUP BY clause
Filter groups with HAVING clause based on a search condition

Using Subqueries

Write Self-contained and correlated subqueries
Explain scalar or multi valued results
Use EXISTS predicate with subqueries

Using Table Expressions

Applying Views
Use derived tables to write queries
Understand common table expressions
Explain Inline Table-Valued Functions
Create simple inline table valued functions

Applying Set Operators

Combining data using UNION operator
Use INTERSECT and EXCEPT operators to write queries
Manipulating rows using APPLY operators

Use of Window Ranking, Offset, and Aggregate Functions

Understand the benefits to use window functions
Use OVER for creating Windows
Analyse Window Functions

Pivoting and Grouping Sets

Write queries with PIVOT and UNPIVOT
Specify multiple groupings
Work with grouping sets

Querying data with Stored Procedures

Classify PIVOT and UNPIVOT
Pass parameters to stored procedures
Design simple stored procedures
Perform Dynamic SQL with EXEC

Programming with T-SQL

Understand Programming elements of T-SQL
Controlling program flow using IF and WHILE blocks
Understand batches
Allocating synonyms and variables

Implementing Error Handling

Understand Behavior of SQL Server
Use TRY/CATCH block
Work with error information
Identify user defined errors
Explain pass system errors in T-SQL code

Implementing Transactions

Classify Transactions and Database engine
Understand differences between batches and transactions
Use transaction control language statements to manage transactions
Apply SET XACT_ABORT
Explore Isolation Levels and its effect on transactions

Improving Query Performance

Classify the elements of well-performing queries
Describe basic query performance data

Querying SQL Server Metadata

Performing System Stored Procedures
Understand functions of Querying System Catalog
Describe Dynamic Management Objects

Querying Microsoft SQL Server 2014 (M20461) Enquiry

Enquire Now

----- OR -------

Reach us at 0121 368 7851 or info@msptraining.com for more information.

ABOUT Milton Keynes

Milton Keynes is a town in Buckinghamshire, South East England, that lies equidistant from London, Birmingham, Oxford, Cambridge and Leicester. Although the town was found as recent as January 1967 to provide for the more residential area, archaeologists have traced its existence back to the Bronze Age. When the town of Milton Keynes came into existence in 1967 it took under its umbrella the existing towns of Bletchley, Wolverton and Stony Stratford. The town got its name from the Middle Age village of Milton Keynes which was mentioned as Middeltone in the 11^th Century.

The town has a population of 250,00 as of now which is almost 5 times than it was when it came into existence in 1967.The design of this town was created by known urban planners and architects including Lord Norman Foster, Sir Richard MacCormac, Ralph Erskine, Henning Larsen, Martin Richardson and John Winter. The design the created was of a modernist type and used the grid square system.

The town is home to various parkland and lakes which are now known to characterize Milton Keynes now. Though one may find very tall buildings in Milton Keynes today, this was not so until 2004. Earlier, as a rule, no building could be taller than a tree.Milton Keynes is a combination of both the ancient and modern attracts visitors and new residents alike.

Villages and Towns in Milton Keynes

Bletchley - first recorded as Blechelai in the 12th century was a major Victorian junction that was the main cause of its development during that period. The villages of Water Eaton and Fenny Stratford also became a part of Milton Keynes later on.
New Bradwell – It was built for the railway workers especially to the north of Bradwell, across the canal and the railway, to the east of Wolverton. The old Wolverton to Newport Pagnell Line has now been converted to a Redway, a favourite for cyclists and so named because of the red pathways.
Great Linford - mentioned as Linford in the in the Domesday Book as Linford, features a church dating to 1215 AD that is dedicated to Saint Andrew. What is now an arts museum used to be the outer buildings of the 17th-century manor house. The Linford Manor has also become a well-known studio for recording.

Existing Districts and Councils of Milton Keynes

Bletchley and Fenny Stratford:, Central Bletchley, Denbigh North, Water Eaton, Denbigh East, Brick fields Denbigh West Fenny Stratford, Granby, Mount Farm, Newton Leys, Fenny Lock
Bradwell: Bradwell village, Bradwell, Bradwell Common, Rooksley, Heelands
Bradwell Abbey: Rooksley, Kiln Farm, Two Mile Ash, Wymbush, Stacey Bushes
Broughton and Milton Keynes: Middleton (including Milton Keynes Village), Pineham, Atterbury, Brook Furlong, Broughton, Fox Milne, Oakgrove, Northfield
Campbell Park: Springfield, Fishermead, Newlands, Oldbrook, Woolstone, Winterhill, Willen and Willen Lake
Central Milton Keynes: Campbell Park and Central Milton Keynes
Great Linford: Blakelands, Conniburrow, Downs Barn, Giffard Park, Bolbeck Park, Great Linford, Pennyland, Downhead Park, Neath Hill, Willen Park, Tongwell
Kents Hill, Monkston and Brinklow: Kents Hill, Brinklow, Monkston, Kingston
New Bradwell
Loughton: Great Holm, Loughton Lodge, Loughton, Knowlhill
Old Woughton: Woughton Park, Woughton on the Green, Passmore
Shenley Brook End: Furzton, Emerson Valley, Kingsmead, Snelshall, Shenley Brook End, Westcroft, Tattenhoe Park, Tattenhoe,
Shenley Church End: Grange Farm, Crownhill, Melbourne, Hazeley, Oxley Park, Oakhill, Woodhill, Shenley Church End
Simpson: West Ashland, Simpson, Ashland
Stantonbury: Blue Bridge, Bancroft/Bancroft Park, Linford Wood, Bradville, Stantonbury Field, Stantonbury, Oakridge Park
Stony Stratford: Galley Hill, Fullers Slade
Walton: Walton, Walton Hall, Caldecotte, Old Farm Park, Brown's Wood, Tilbrook, Tower Gate, Walnut Tree, Walton Park, Wavendon Gate.
West Bletchley: Denbigh Hall, Old Bletchley, Far Bletchley, West Bletchley
Wolverton and Greenleys: Wolverton, Old Wolverton, Greenleys, Hodge Lea, Stonebridge
Woughton: Netherfield, Beanhill, Peartree Bridge, Bleak Hall, Elfield Park, Coffee Hall, Eaglestone, Leadenhall, Redmoor, Tinkers Bridge.

TRAINING AVAILABILITY

Aberdeen

Bath

Bedford

Belfast

Birmingham

Bolton

Bournemouth

Bracknell

Brighton

Bristol

Bromley

Burton Upon Trent

Buxton

Cambridge

Canterbury

Cardiff

Carlisle

Chatham

Chelmsford

Chester

Chichester

Chorley

Cirencester

Colchester

Corby

Coventry

Crewe

Darlington

Derby

Dereham

Doncaster

Dublin

Dundee

Dunfermline

Eastleigh

Edinburgh

Exeter

Fleet

Gatwick

Glasgow

Gloucester

Guildford

Harrogate

Heathrow

High Wycombe

Hinckley

Inverness

Ipswich

Jersey

Kingston upon Hull

Leeds

Leicester

Lincoln

Liverpool

London

Luton

Maidstone

Manchester

Middlesbrough

Newcastle

Northampton

Norwich

Nottingham

Oxford

Peterborough

Plymouth

Poole

Portsmouth

Preston

Reading

Sheffield

Slough

Solihull

Southampton

Stoke-on-Trent

Sunderland

Swansea

Swindon

Teesside

Tonbridge

Tredegar

Wakefield

Warrington

Warwick

Watford

Windsor Town

Wokingham

Wolverhampton

Worcester

Wrexham

York